"""
Case Type   : 基础算子性能提升-快速参数化增加视图查询功能
Case Name   : 2个会话同时连接，各自视图缓存记录，互相独立不受影响
Create At   : 2025-10-17
Owner       : sungang14
Description :
    1、set enable_query_parameterization = on;
    2、在session1中创建表并执行insert语句
    3、查看视图
    4、在session2中创建表并执行insert语句
    5、查看视图
Expect      :
    1、设置成功
    2、成功
    3、只有有对应session1的缓存记录
    4、成功
    5、只有对应session2的缓存记录
History     :
"""

import os
import unittest
from testcase.utils.ComThread import ComThread
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.show_query_parameterization = 'show enable_query_parameterization;'
        self.set_query_parameterization = "enable_query_parameterization = ON"
        self.set_query_parameterization_01 = "enable_query_parameterization = OFF"
        self.table_name1 = f"query_parameterization_views_case0004_1"
        self.table_name2 = f"query_parameterization_views_case0004_2"
        self.table_name = f"query_parameterization_views_case0004"
        self.prepare_data_00_1 = f"drop table if exists {self.table_name1} cascade;"
        self.prepare_data_00_2 = f"drop table if exists {self.table_name2} cascade;"
        self.prepare_data_01_1 = f"""CREATE TABLE {self.table_name1}
                            (
                                c_customer_sk integer,
                                c_customer_id char(5),
                                c_first_name char(6),
                                Amount integer
                            );
                            INSERT INTO {self.table_name1} VALUES (3769, 'hello', 'Grace', 1000);
                            select * from query_parameterization_views() where parameterized_query like '%{self.table_name}%';"""
        self.prepare_data_01_2 = f"""CREATE TABLE {self.table_name2}
                            (
                                c_customer_sk integer,
                                c_customer_id char(5),
                                c_first_name char(6),
                                Amount integer
                            );
                            INSERT INTO {self.table_name2} VALUES (3770, 'hello', 'Grace1', 1001);
                            select * from query_parameterization_views() where parameterized_query like '%{self.table_name}%';"""
        text = '--step1:开启enable_query_parameterization参数;expect:开启成功--'
        logger.info(text)
        show_cmd = self.comsh.execut_db_sql(self.show_query_parameterization)
        logger.info(show_cmd)
        self.assertEquals('off', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        set_cmd1 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_query_parameterization)
        logger.info(set_cmd1)

    def test_slowsql(self):
        text = '--step2:在session1中创建表并执行insert语句;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00_1)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        thread_1 = ComThread(self.comsh.execut_db_sql, args=(self.prepare_data_01_1, ''))
        thread_1.setDaemon(True)
        thread_1.start()

        text = '--step3:在session2中创建表并执行insert语句;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00_2)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        thread_2 = ComThread(self.comsh.execut_db_sql, args=(self.prepare_data_01_2, ''))
        thread_2.setDaemon(True)
        thread_2.start()

        text = '--step4:查看query_parameterization_views;expect:仅有insert的一条记录--'
        logger.info(text)
        thread_1.join(30)
        msg_result_1 = thread_1.get_result()
        logger.info(msg_result_1)

        thread_2.join(30)
        msg_result_2 = thread_2.get_result()
        logger.info(msg_result_2)
        self.assertIn(f'1 row', msg_result_1, '执行失败' + text)
        self.assertIn(f'| INSERT     |', msg_result_1, '执行失败' + text)
        self.assertIn(f'1 row', msg_result_2, '执行失败' + text)
        self.assertIn(f'| INSERT     |', msg_result_2, '执行失败' + text)

    def tearDown(self):
        text = '--step5:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00_1)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00_2)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        set_cmd1 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_query_parameterization_01)
        logger.info(set_cmd1)
        show_cmd = self.comsh.execut_db_sql(self.show_query_parameterization)
        logger.info(show_cmd)
        self.assertEquals('off', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
